import time
import openpyxl
import paramiko
import re
from openpyxl import Workbook,load_workbook
from datetime import datetime

# 华三v7采集端口状态
wb1 = openpyxl.Workbook()
wb = load_workbook('D:/git/Zhangqiuxian/python/交换机测试/switch.xlsx')
sheet = wb['H3Cv7']
for info in range(2,sheet.max_row+1):
    host_name = sheet.cell(info,1).value
    ip = sheet.cell(info,2).value
    user_name = sheet.cell(info,3).value
    passwd = sheet.cell(info,4).value
    print("正在连接 "+host_name+" "+str(ip))
    ssh_client = paramiko.SSHClient()
    ssh_client.set_missing_host_key_policy(paramiko.AutoAddPolicy())
    ssh_client.connect(hostname=ip,port=22,username=user_name,password=passwd)
    cli = ssh_client.invoke_shell()
    time.sleep(1)
    cli.send('n\n')
    cli.send('screen-length disable\n')
    cli.send('display cu inter | in int\n')
    time.sleep(10)
    cut_out = cli.recv(999999).decode()
    print('已经获取到配置，正在采集信息')
    interface_list = re.findall('interface(.*\S+)',cut_out)
    print('找到' + str(len(interface_list)) + '个端口')   
    ws1 = wb1.create_sheet(host_name)
    ws1.append(['接口', '接口类型', 'pvid', '接口状态', 'ip地址', '接口描述', '采集时间'])
    for interface_info in interface_list:
        cli.send('display interface ' + interface_info + ' \n')
        time.sleep(0.5)
        new = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        interface_info1 = cli.recv(999999).decode()
#        time.sleep(0.1)
        interface_state = re.findall('Current state:(.*\w+)', interface_info1)
        interface_type = re.findall('Port link-type:(.*\S+)',interface_info1)
        interface_pvid = re.findall('PVID:(.*\d+)',interface_info1)
        interface_desc = re.findall('Description:(.*\S+)',interface_info1)
        interface_ip = re.findall('Internet address:(.*\S+)',interface_info1)
        ws1.cell(interface_list.index(interface_info)+2,1).value = str(interface_info)
        ws1.cell(interface_list.index(interface_info)+2,2).value = "".join(interface_type)
        ws1.cell(interface_list.index(interface_info)+2,3).value = "".join(interface_pvid)
        ws1.cell(interface_list.index(interface_info)+2,4).value = "".join(interface_state)
        ws1.cell(interface_list.index(interface_info)+2,5).value = "".join(interface_ip)
        ws1.cell(interface_list.index(interface_info)+2,6).value = "".join(interface_desc)
        ws1.cell(interface_list.index(interface_info)+2,7).value = "".join(new)
        print('获取到' + interface_info + ' 接口的信息')
    print(host_name + ' 采集完成')
    cli.close()
wb1.save('D:/git/Zhangqiuxian/python/交换机测试/' + str(sheet.title) + '-' + datetime.now().strftime('%Y-%m-%d') + '.xlsx')
